import xlrd  # xls读取
import xlwt  # xls写入

def write_exc(datass, filename='any'):
    wb = xlwt.Workbook()
    i = 0
    for datas in datass:
        ws = wb.add_sheet('new'+str(i))
        for row in range(len(datas)):
            for col in range(len(datas[row])):
                ws.col(col).width = 256*12
                ws.write(row, col, datas[row][col])
        i += 1
    filename = str(filename) + '.xls'
    wb.save(filename)


# 数据行列变换
def row_to_col(datas):
    temp = []
    for i in range(len(datas[0])):
        tmp = []
        for j in range(len(datas)):
            tmp.append(datas[j][i])
        temp.append(tmp)
    return temp

# 参数 缺项效验
def parm_req(s, tmp, flag=0):
    '''
    0-去除参数值
    1-去除参数及值
    2-获取参数值
    str-填写内容将追加在本参数值末位
    '''

    base = s.rfind(tmp)
    t = s[base:]
    spos = t.find('=') + base
    epos = t.find('&')

    tf = 1
    if epos == -1:
        base = base - 1
        epos = None
        tf = 0
    else:
        epos += base

    if flag == 0:
        result = s[:spos + 1]
        if tf:
            result += s[epos:]
    elif flag == 1:
        result = s[:base]
        if tf:
            result += s[epos + 1:]
    elif flag == 2:
        result = s[spos + 1:epos]
    else:
        if epos is None:
            result = s[:epos] + flag
        else:
            result = s[:epos] + flag + s[epos:]

    return result


# 边界值 0-() 1-[]
def boundary_value(s, precision=1):
    '''
    读入字符串s，判断其中内容，

    ()[](][)-被这四种形式包含的，内容将会被认为是数值区间，
    例:[20,30),边界值小的部分为20，闭区间，大的部分为30，开区间，

    __-被下划线包袱的，内容将被认为是精度precision

    最终产出为[[开闭类型，小值],[开闭类型，大值],精度]
    '''

    sflag = 0
    spos = s.find('(')
    if spos == -1:
        sflag = 1
        spos = s.find('[')
        if spos == -1:
            return None

    eflag = 0
    epos = s.find(')')
    if epos == -1:
        eflag = 1
        epos = s.find(']')
        if epos == -1:
            return None

    t = s[spos + 1:epos]
    num_min = t[:t.find(',')]
    num_max = t[t.find(',') + 1:]

    spos = s.find('_')
    t = s[spos + 1:]
    epos = t.find('_')
    if epos != -1:
        precision = t[:epos]

    data = [[sflag, int(num_min)], [eflag, int(num_max)], float(precision)]
    return data


# 接口测试 接口说明书
class Interface_test():

    def __init__(self, from_way, to_way, host='http://192.168.75.128:90'):
        self.from_way = from_way
        self.to_way = to_way
        # 主页
        self.host = host

        # 表格宽度 自0至最后输出的位数
        self.tb_width = [5, 12, 30, 20, 10, 30, 8, 10, 35, 10, 10, 12, 10, 10, 12, 5, 12, 10, 40]

        # 表头 [[背景色号, 标签内容]...]
        self.header = [[30, ['编号', '模块', '前置条件', '测试点']],
                       [51, ['接口名称', 'url地址', '请求方式', '特殊请求头', '请求参数', '请求报文']],
                       [50, ['状态码', '期望业务状态码', '测试结果', '测试人员', ' 测试日期']]]

        # 标签名 用于复刻接口说明书内容
        self.tag = ['接口名称', 'url地址', '请求方式', '特殊请求头', '触发场景', '入参参数',
                    '数据类型', '备注', '出参']

        self.status = {'success': '200', 'dataE': '400'}
        # '"code":300', '"code":400', '"code":500'
        self.flag = {'success': '"code":100', 'fail': '"code":200', }

        self.wb = xlwt.Workbook()
        self.style = self.style_ini()
        self.style.alignment.wrap = 1  # 自动换行

    # 表格宽度调整
    def tb_width_adj(self, ws):
        lis = self.tb_width
        for i in range(len(lis)):
            ws.col(i).width = 256 * lis[i]

    def write_manual(self, ws, lis, style1, style2, col=16):
        # 打印表格数据
        j = 0
        for row in lis:
            k = col
            for da in row:
                if da in self.tag:
                    self.style_pattern(style2, 5)
                    ws.write(j, k, da, style2)
                else:
                    if da == '' and k > 0:
                        ws.write_merge(j, j, k - 1, k, lis[j][k - col - 1], style1)
                    else:
                        ws.write(j, k, da, style1)
                k += 1
            j += 1

    # 从接口说明书读取内容 【封装格式】[[分表名, 表数据],[分表名, 表数据]...]
    def read_manual(self, fileway):
        exc = []
        wwb = xlrd.open_workbook(fileway)
        for i in range(len(wwb.sheets())):
            name = str(wwb.sheets()[i])
            model_name = name[name.find('<') + 1:-1]

            wws = wwb.sheet_by_index(i)
            tmp = []
            for j in range(wws.nrows):
                tmp.append(wws.row_values(j))
            exc.append([model_name, tmp])
        return exc

    # 为表格样式填充骨架
    def style_ini(self):
        style = xlwt.XFStyle()
        borders = xlwt.Borders()
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        style.borders = borders
        return style

    # 为表格样式填充背景色
    def style_pattern(self, style, bgcolor):
        pattern = xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = bgcolor
        style.pattern = pattern

    # 空行生成
    def elg(self, ws, row=1):
        for i in range(15):
            ws.write(row, i, '', self.style)

    # 模板化参数测试 【lis】[[参数名,数据类型,释义]]
    def parameter_test(self, lis):

        for i in range(len(lis)):
            for j in range(len(lis[i])):
                lis[i][j] = lis[i][j].strip()

        data_base = lis[0][0]
        tmp = []
        temp = []

        flag = 1
        tip = '多参数'
        para = data_base + '&a=1'
        tmp.append([flag, tip, para, self.status['dataE'], self.flag['fail']])

        for i in range(1, len(lis)):
            flag = 0
            tip = '参数必填项-' + lis[i][0]
            para = parm_req(data_base, lis[i][0], 0)
            tmp.append([flag, tip, para, self.status['success'], self.flag['fail']])

            flag = 1
            tip = '参数缺失-' + lis[i][0]
            para = parm_req(data_base, lis[i][0], 1)
            tmp.append([flag, tip, para, self.status['dataE'], self.flag['fail']])

            # 对于数字
            if lis[i][1] == 'int' or lis[i][1] == 'float' or lis[i][1] == 'double':
                flag = 3
                tip = '数据类型-' + lis[i][0]
                data = parm_req(data_base, lis[i][0], 2)
                para = parm_req(data_base, lis[i][0], 1) + '&' + lis[i][0] + '=%20' + data + '%20'
                tmp.append([flag, tip, para, self.status['dataE'], self.flag['fail']])

                tip = '数据类型-' + lis[i][0]
                data = '-1'
                para = parm_req(data_base, lis[i][0], 1) + '&' + lis[i][0] + '=' + data
                tmp.append([flag, tip, para, self.status['success'], self.flag['fail']])

                li = boundary_value(lis[i][2])
                if li is None:
                    pass
                else:
                    flag = 4
                    f = [li[0][0], li[1][0]]  # 开闭
                    num = [li[0][1], li[1][1]]  # 数字
                    precision = li[2]  # 精度

                    # 小
                    n = num[0]
                    code = self.flag['fail']
                    for k in range(3):
                        data = round(n + k * precision - precision, 5)
                        if lis[i][1] == 'int':
                            data = int(data)
                        tip = '边界值-' + lis[i][0] + '//' + str(n) + '_' + str(data)
                        para = parm_req(data_base, lis[i][0], 1) + '&' + lis[i][0] + '=' + str(data)

                        if k + f[0] > 1:
                            code = self.flag['success']
                        tmp.append([flag, tip, para, self.status['success'], str(code)])

                    # 大
                    n = num[1]
                    code = self.flag['success']
                    for k in range(3):
                        data = round(n + k * precision - precision, 5)
                        if lis[i][1] == 'int':
                            data = int(data)
                        tip = '边界值-' + lis[i][0] + '//' + str(n) + '_' + str(data)
                        para = parm_req(data_base, lis[i][0], 1) + '&' + lis[i][0] + '=' + str(data)

                        if k - f[1] > 0:
                            code = self.flag['fail']
                        tmp.append([flag, tip, para, self.status['success'], str(code)])

            flag = 5
            # sql注入
            tip = 'sql注入-' + lis[i][0]
            para = parm_req(data_base, lis[i][0], "' or '1' = '1")
            tmp.append([flag, tip, para, self.status['dataE'], self.flag['fail']])

        flag = 2
        tip = '无参数'
        tmp.append([flag, tip, None, self.status['dataE'], self.flag['fail']])

        temp.append(tmp)
        return temp

    '''根据接口说明书-固定格式，生成简要测试用例及文档'''

    def interface_test_model(self, manual=None):
        # 从接口说明书读取内容
        lis = self.read_manual(self.from_way)

        # 开始根据读取内容进行表格绘制
        n = 0
        for i in lis:
            for row in range(len(i[1])):
                for col in range(len(i[1][row])):
                    i[1][row][col] = i[1][row][col].strip('\n')
                    i[1][row][col] = i[1][row][col].strip()

            # 新建分表
            ws = self.wb.add_sheet(i[0], cell_overwrite_ok=True)

            # 对表的宽度进行调整
            self.tb_width_adj(ws)

            style1 = self.style_ini()
            style2 = self.style_ini()

            # 写入表头
            j = 0
            for data in self.header:
                self.style_pattern(style2, data[0])

                for da in data[1]:
                    ws.write(0, j, da, style2)
                    j += 1

            # 写入初始化内容
            tmp = []
            r = 1
            self.elg(ws, 1)
            tpos = i[0].find('-')
            if tpos == -1:
                tpos = None

            ws.write(1, 0, r, style1)
            in_model = i[0][:tpos]
            ws.write(1, 1, i[0][:tpos], style1)
            ws.write(1, 3, '正常流程成功测试', style1)
            ws.write(1, 10, self.status['success'], self.style)
            ws.write(1, 11, self.flag['success'], self.style)

            for row in range(len(i[1])):
                if i[1][row][0] == '接口名称':
                    in_name = i[1][row][1]
                    ws.write(1, 4, i[1][row][1], self.style)
                elif i[1][row][0] == 'url地址':
                    in_url = self.host + i[1][row][1]
                    ws.write(1, 5, in_url, self.style)
                elif i[1][row][0] == '请求方式':
                    in_req = i[1][row][1]
                    ws.write(1, 6, i[1][row][1], self.style)
                elif i[1][row][0] == '入参参数':
                    row += 1
                    while i[1][row][0] != '出参':
                        if i[1][row][0] == '':
                            pass
                        else:
                            tmp.append(i[1][row])
                        row += 1

                    if tmp == []:
                        pass
                    else:
                        ws.write(1, 8, tmp[0][0], self.style)

                        datas = self.parameter_test(tmp)
                        for k in range(10):
                            for data in datas:
                                for ro in data:
                                    if ro[0] == k:
                                        r += 1
                                        self.elg(ws, r)
                                        ws.write(r, 0, r, style1)
                                        ws.write(r, 1, in_model, self.style)
                                        ws.write(r, 4, in_name, self.style)
                                        ws.write(r, 5, in_url, self.style)
                                        ws.write(r, 6, in_req, self.style)

                                        ws.write(r, 3, ro[1], self.style)
                                        ws.write(r, 8, ro[2], self.style)
                                        ws.write(r, 10, ro[3], self.style)
                                        ws.write(r, 11, ro[4], self.style)

            if manual is not None:
                # 书写接口说明书
                self.write_manual(ws, i[1], style1, style2)

            n += 1

        self.wb.save(self.to_way)


# 接口说明书
class Interface_Manual():

    def __init__(self, from_way, to_way):
        self.from_way = from_way
        self.to_way = to_way

    # 从接口测试读取内容 【封装格式】[[分表名, 表数据],[分表名, 表数据]...]
    def read_manual(self):
        exc = []
        wwb = xlrd.open_workbook(self.from_way)
        for i in range(len(wwb.sheets())):
            name = str(wwb.sheets()[i])
            model_name = name[name.find('<') + 1:-1]

            wws = wwb.sheet_by_index(i)
            tmp = []
            for j in range(16, wws.ncols):
                tmp.append(wws.col_values(j))

            # 消除空行
            if len(tmp) == 3:
                max = len(tmp[0])
                k = 0
                while max != k:
                    if tmp[0][k] == '' and tmp[1][k] == '' and tmp[2][k] == '':
                        del tmp[0][k], tmp[1][k], tmp[2][k]
                        max -= 1
                        k -= 1
                    k += 1
            exc.append([model_name, tmp])
        return exc

    # 将datas写入说明书，按照格式
    def write_manual(self, datas):
        exc = Interface_test(self.from_way,self.to_way)
        for i in range(len(datas)):
            data = row_to_col(datas[i][1])
            ws = exc.wb.add_sheet(datas[i][0], cell_overwrite_ok=True)

            exc.tb_width = [20, 40, 40]
            exc.tb_width_adj(ws)

            style1 = exc.style
            style2 = exc.style_ini()
            exc.style_pattern(style2, 5)
            exc.write_manual(ws, data, style1=style1, style2=style2, col=0)
            exc.wb.save(self.to_way)

    def model_(self):
        exc = Interface_test()
        data = ['model',
                [['接口名称', 'xx接口', ''], ['url地址', 'http://', ''], ['请求方式', 'GET', ''], ['特殊请求头', 'jsessionid', ''],
                 ['触发场景', '操作', ''], ['入参参数', '数据类型', '备注'], ['参数1', '字符串', 'xxx'], ['参数2 ', '字符串', 'xxx'],
                 ['出参', '数据类型', '备注'], ['code', '数字', '状态码：\n100：登陆成功\n200：登陆失败，用户名或者密码错误'],
                 ['msg', '字符串', '消息，针对code具体解释'], ['extend', 'json数据', '扩展数据：\n登陆成功后，所要具体的扩展数据\nurl地址']]]
        ws = exc.wb.add_sheet(data[0], cell_overwrite_ok=True)

        exc.tb_width = [20, 40, 40]
        exc.tb_width_adj(ws)

        style1 = exc.style
        style2 = exc.style_ini()
        exc.style_pattern(style2, 5)
        exc.write_manual(ws, data[1], style1=style1, style2=style2, col=0)
        exc.wb.save(self.to_way)

# manu = Interface_Manual(r'E:\BaiduNetdiskDownload\汉科软_实训软件\项目\hkr-thr\垃圾框架\TEST\Asset\接口测试.xls',
#                         r'E:\BaiduNetdiskDownload\汉科软_实训软件\项目\hkr-thr\垃圾框架\TEST\Asset\接口说明书.xls')
# datas = manu.read_manual()
# manu.write_manual(datas)

# manu = Interface_test(r'E:\BaiduNetdiskDownload\汉科软_实训软件\项目\hkr-thr\垃圾框架\TEST\Asset\接口说明书.xls',
#                         r'E:\BaiduNetdiskDownload\汉科软_实训软件\项目\hkr-thr\垃圾框架\TEST\Asset\接口测试.xls',
#                       host='http://101.42.249.74:90')
# manu.interface_test_model(1)


